import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import plotly.express as px
import seaborn as sns
! pip install pandas sqlalchemy mysql-connector-python
from sqlalchemy import create_engine
import pandas as pd

Requirement already satisfied: pandas in c:\users\hp\anaconda3\lib\site-packages (2.2.2)
Requirement already satisfied: sqlalchemy in c:\users\hp\anaconda3\lib\site-packages (2.0.30)
Requirement already satisfied: mysql-connector-python in c:\users\hp\anaconda3\lib\site-packages (9.5.0)
Requirement already satisfied: numpy>=1.26.0 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (1.26.4)
Requirement already satisfied: python-dateutil>=2.8.2 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2.9.0.post0)
Requirement already satisfied: pytz>=2020.1 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2024.1)
Requirement already satisfied: tzdata>=2022.7 in c:\users\hp\anaconda3\lib\site-packages (from pandas) (2023.3)
Requirement already satisfied: typing-extensions>=4.6.0 in c:\users\hp\anaconda3\lib\site-packages (from sqlalchemy) (4.15.0)
Requirement already satisfied: greenlet!=0.4.17 in c:\users\hp\anaconda3\lib\site-packages (from sqlalchemy) (3.0.1)
Requirement already satisfied: six>=1.5 in c:\users\hp\anaconda3\lib\site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)

df_meds=pd.read_csv('Final_Pharmacy_Inventory_Extended.csv')

df_meds.columns

Index(['Drug_Name', 'Manufacturer', 'Price', 'Active_Ingredient',
       'Therapeutic_Class', 'Action_Class', 'Primary_Use', 'Is_Habit_Forming',
       'Mfg_Date', 'Expiry_Date', 'Days_Remaining', 'Stock_Qty'],
      dtype='object')

df_meds['Drug_Name'].value_counts()

Drug_Name
Ringer Lactate Infusion        28
NS 0.9% Infusion               24
Cefpoxim Dry Syrup             16
Oral Polio Vaccine             12
Broxine Syrup                  12
                               ..
Fenceta M Syrup                 1
Flood 20mg Capsule              1
Flujoy Tablet                   1
Fexomeg M 10mg/180mg Tablet     1
Zyvocol 1% Dusting Powder       1
Name: count, Length: 249398, dtype: int64

df_drugs_unique=df_meds.copy()
df_drugs_unique = (
    df_drugs_unique[["Drug_Name"]]
    .drop_duplicates()
    .reset_index(drop=True)
)

df_drugs_unique["DrugID"] = range(1, len(df_drugs_unique) + 1)

df_drugs_unique

df_meds = df_meds.merge(df_drugs_unique, on="Drug_Name", how="left")

df_meds.columns

Index(['Drug_Name', 'Manufacturer', 'Price', 'Active_Ingredient',
       'Therapeutic_Class', 'Action_Class', 'Primary_Use', 'Is_Habit_Forming',
       'Mfg_Date', 'Expiry_Date', 'Days_Remaining', 'Stock_Qty', 'DrugID'],
      dtype='object')

import numpy as np
import pandas as pd

df_meds_1 = df_meds.copy()
df_drugs_1 = df_drugs_unique.copy()
    

dates = pd.date_range(start="2024-01-01", periods=365*2)
sales_rows = []

np.random.seed(42)


for day in dates:
    daily_sales_count = np.random.randint(100,500)
    meds_today = (
        df_meds_1.sample(daily_sales_count, replace=True)
                     .reset_index(drop=True)
    )

    qty_sold = np.random.randint(1, 15, size=daily_sales_count)

    for idx, row in meds_today.iterrows():
        sales_rows.append([
            day,
            row["DrugID"],              
            row["Drug_Name"],         
            qty_sold[idx],              
            row["Price"],                 
            qty_sold[idx] * row["Price"] 
        ])

df_med_sales = pd.DataFrame(
    sales_rows,
    columns=[
        "Date",
        "DrugID",
        "Drug_Name",
        "Quantity_Sold",
        "Unit_Price",
        "Total_Amount"
    ]
)

df_med_sales.head()

df_meds.head()

df_meds.to_csv('meds_inventory.csv',index=False)

df_med_sales.to_csv('med_sales.csv',index=False)

df_med_sales

DB_USER = "root"
DB_PASSWORD = "Brahmapur@1234"
DB_HOST = "127.0.0.1"  
DB_PORT = 3306
DB_NAME = "pharmac_inventory"

from urllib.parse import quote_plus

ENCODED_PASSWORD = quote_plus(DB_PASSWORD)

db_url = f"mysql+mysqlconnector://{DB_USER}:{ENCODED_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

engine = create_engine(db_url, echo=False)

file1 = 'med_sales.csv'
tab1  = 'med_sales'

file2 = 'meds_inventory.csv'
tab2  = 'meds_inventory'

def load_csv_to_mysql(file_path, table_name, engine):
    try:
        df = pd.read_csv(file_path)

        df.to_sql(
            name=table_name,
            con=engine,          
            if_exists='replace',
            index=False,
            method='multi'     
        )

        print(f"Loaded {file_path} into table `{table_name}` successfully")

    except Exception as e:
        print(f"An error occurred loading {file_path}: {e}")

if __name__ == "__main__":
    load_csv_to_mysql(file1, tab1, engine)
    load_csv_to_mysql(file2, tab2, engine)

    engine.dispose()

Loaded med_sales.csv into table `med_sales` successfully
Loaded meds_inventory.csv into table `meds_inventory` successfully

df_meds = df_meds.sort_values(["DrugID", "Mfg_Date"])

df_meds["Mfg_Date"] = pd.to_datetime(df_meds["Mfg_Date"])
df_meds["Expiry_Date"] = pd.to_datetime(df_meds["Expiry_Date"])
df_med_sales["Date"] = pd.to_datetime(df_med_sales["Date"])

def get_valid_batch(drug_id, sale_date):
    batches = df_meds[df_meds["DrugID"] == drug_id]
    valid = batches[batches["Mfg_Date"] <= sale_date]
    if valid.empty:
        return None  
        
    return valid.sort_values("Mfg_Date").iloc[-1]

df_meds["BatchID"] = range(1, len(df_meds) + 1)

df_med_sales["SaleID"]=df_med_sales.index

df_med_sales=df_med_sales.merge(df_meds,on='DrugID',how='left')

df_med_sales = df_med_sales[(df_med_sales["Mfg_Date"] <= df_med_sales["Date"])&(df_med_sales['Expiry_Date']> df_med_sales['Date']) ]

df_med_sales = df_med_sales.sort_values(["SaleID", "Mfg_Date"])
df_med_sales = df_med_sales.drop_duplicates(subset="SaleID", keep="last") # keeping the latest batch as per the sales date and mfg date 
# keeps the last occurance (row) from multiple merged rows for a sale id , drug id .

df_med_sales=df_med_sales[[
    'Date', 'DrugID', 'Drug_Name_x', 'Quantity_Sold', 'Unit_Price',
       'Total_Amount', 'SaleID',
     'Mfg_Date', 'Expiry_Date',
       'Stock_Qty', 'BatchID']]

df_med_sales.columns=['Date', 'DrugID', 'Drug_Name', 'Quantity_Sold', 'Unit_Price',
       'Total_Amount', 'SaleID', 'Mfg_Date', 'Expiry_Date', 'Initial_Stock',
       'BatchID']

df_med_sales=df_med_sales.sort_values(['DrugID','Date'])

df_med_sales["Cumulative_Sold"] = df_med_sales.groupby("DrugID")["Quantity_Sold"].cumsum()

df_med_sales['Remaining_Stock']=df_med_sales["Initial_Stock"] - df_med_sales["Cumulative_Sold"]

df_med_sales["Stockout_Flag"] = (df_med_sales["Remaining_Stock"] <= 0) | (df_med_sales['Remaining_Stock']< df_med_sales['Quantity_Sold'])

stockout = (
    df_med_sales[df_med_sales["Stockout_Flag"] == True]
    .groupby(["DrugID",'Drug_Name'])
    .size()
    .reset_index(name="Stockout_Count")
)
stockout.sort_values(by="Stockout_Count",ascending=False).head(20)

top10 = stockout.head(10)["DrugID"]
fast_moving10 = df_med_sales[df_med_sales["DrugID"].isin(top10)]

import plotly.graph_objects as go

fig = go.Figure()

for drug in fast_moving10["Drug_Name"].unique():
    sub = fast_moving10[fast_moving10["Drug_Name"] == drug]

    fig.add_trace(
        go.Scatter(
            x=sub["Date"],
            y=sub["Cumulative_Sold"],
            mode="lines+markers",
            name=drug,
            hovertemplate=
                "<b>Date:</b> %{x}<br>" +
                "<b>Drug:</b> " + drug + "<br>" +
                "<b>Units Sold:</b> %{y}<br>" +
                "<b>Remaining Stock:</b> %{customdata}<extra></extra>",
            customdata=sub["Remaining_Stock"]
        )
    )

fig.update_layout(
    title="Daily Sales Pattern for Top 10 Fast-Moving Drugs",
    xaxis_title="Date",
    yaxis_title="Units Sold",
    legend_title="Drug Name",
    hovermode="closest",  
    height=600
)

fig.show()

df_med_sales["Month"] = df_med_sales["Date"].dt.to_period("M").astype(str)

stockout_matrix = (
    df_med_sales[df_med_sales["Stockout_Flag"] == True]
    .groupby(["Drug_Name", "Month"])
    .size()
    .reset_index(name="Stockout_Count")
)

pivot_df = stockout_matrix.pivot(index="Drug_Name", columns="Month", values="Stockout_Count").fillna(0)

fig = px.imshow(
    pivot_df,
    labels=dict(x="Month", y="Drug Name", color="Stockout Intensity"),
    title="Heatmap: Drug vs Month — Stock-out Intensity",
    aspect="auto",
    color_continuous_scale="Reds"   
)

fig.update_layout(
    xaxis=dict(side="top"),
    height=800
)

fig.show()

df_meds['Loss_by_expiry']= np.where(
    df_meds["Days_Remaining"] < 0,
    df_meds["Stock_Qty"] * df_meds["Price"] * -1,
    0
)

expired_drugs=df_meds.groupby(['Drug_Name']).aggregate({'Loss_by_expiry':"sum"}).sort_values("Loss_by_expiry",ascending=True).reset_index()
expired_drugs.head(10)

from IPython.display import HTML

total_loss_bn = df_meds["Loss_by_expiry"].sum() / 1_000_000000 * -1
HTML(f"<h1 style='font-size:40px; color:#d9534f;'>Total Loss by Expired drugs : {total_loss_bn:.2f} Billion</h1>")

df_med_sales['Loss_by_stockout']= np.where(
    df_med_sales["Stockout_Flag"]==True,
    (df_med_sales["Quantity_Sold"]-df_med_sales['Remaining_Stock']) * df_med_sales["Unit_Price"] * -1 ,
    0
)

stockout_loss=df_med_sales.groupby(['Drug_Name']).aggregate({'Loss_by_stockout':"sum"}).sort_values("Loss_by_stockout",ascending=True).reset_index()
stockout_loss.head(10)

from IPython.display import HTML

total_loss_th = df_med_sales["Loss_by_stockout"].sum() / 1_000 * -1
HTML(f"<h1 style='font-size:40px; color:#d9534f;'>Total Loss by Stockout drugs: {total_loss_th:.2f} Thousand </h1>")

top_losses = expired_drugs.head(50)["Drug_Name"]
drugs_at_loss = df_meds[df_meds["Drug_Name"].isin(top_losses)]

df_top10 = drugs_at_loss.groupby("Drug_Name")["Loss_by_expiry"].sum().sort_values().head(20).reset_index()

fig = go.Figure()

fig.add_trace(
    go.Bar(
        x=df_top10["Loss_by_expiry"],
        y=df_top10["Drug_Name"],
        orientation='h',
        marker_color='red'
    )
)

fig.update_layout(
    title="Top 10 Drugs at Loss by Expiry",
    height=500,
    xaxis_title="Loss Amount",
    yaxis_title="Drug Name",
    template="plotly_white"
)

fig.show()

class_loss = (
    drugs_at_loss.groupby(["Therapeutic_Class", "Drug_Name"])["Loss_by_expiry"]
    .sum()
    .head(50)
    .reset_index()
)

fig = go.Figure()

for drug in class_loss["Drug_Name"].unique():
    sub = class_loss[class_loss["Drug_Name"] == drug]

    fig.add_trace(
        go.Bar(
            x=sub["Therapeutic_Class"],
            y=sub["Loss_by_expiry"],
            name=drug
        )
    )

fig.update_layout(
    barmode='stack',
    title="Stacked Bar Chart: Drugs at  per Therapeutic Class",
    width=1000,
    height=700,
    xaxis_title="Therapeutic Class",
    yaxis_title="Total Loss",
    template="plotly_white",
    legend_title="Drug Name"
)

fig.show()

df_med_sales.drop('Total_Amount',axis=1,inplace=True)
df_med_sales['Total_Amount']=np.where(
    (df_med_sales['Remaining_Stock']<df_med_sales['Quantity_Sold']) & (df_med_sales['Remaining_Stock'] >0),
    df_med_sales['Remaining_Stock']*df_med_sales['Unit_Price'],
    df_med_sales['Quantity_Sold']*df_med_sales['Unit_Price']
)

revenue_df = df_med_sales.groupby("DrugID", as_index=False).agg({"Drug_Name": "first","Total_Amount": "sum","Quantity_Sold": "sum"})

revenue_df.rename(columns={"Total_Amount": "Total_Revenue","Quantity_Sold": "Total_Quantity"}, inplace=True)

revenue_df = revenue_df.sort_values("Total_Revenue", ascending=False).reset_index(drop=True)

revenue_df["Cum_Revenue"] = revenue_df["Total_Revenue"].cumsum()

revenue_df["Cum_Revenue_Percent"] = 100 * revenue_df["Cum_Revenue"] / revenue_df["Total_Revenue"].sum()

def classify_abc(p):
    if p <= 20:
        return "A"
    elif p <= 50:
        return "B"
    else:
        return "C"

revenue_df["ABC_Category"] = revenue_df["Cum_Revenue_Percent"].apply(classify_abc)

abc_revenue = revenue_df.groupby("ABC_Category")["Total_Revenue"].sum().reset_index()

drug_names = (
revenue_df.groupby("ABC_Category")["Drug_Name"]
    .apply(lambda x: ", ".join(sorted(x.unique())))
    .reset_index()
    .rename(columns={"Drug_Name": "Drug_List"})
)

abc_details = abc_revenue.merge(drug_names, on="ABC_Category")

abc_details['Category_count']= abc_details['Drug_List'].apply(len)

abc_details

fig = px.pie(
    abc_details,
    names="ABC_Category",
    values="Total_Revenue",
    title="Revenue Share by ABC Category",
    hover_data={"Total_Revenue": True}
)

fig.update_traces(
    hovertemplate=
    "<b>Category:</b> %{label}<br>" +
    "<b>Revenue:</b> ₹%{value:,.2f}<br>" +
    "<b>Top</b> %{percent}Percent<br>"
)

fig.show()

df_med_sales['Days_Remaining']=df_med_sales['Expiry_Date']-df_med_sales['Date']

from scipy.stats import norm
Lead_Time_Days = 7              
Target_Service_Level = 0.95   # 95% service level means only 5% chance of stockout
Z_Value = norm.ppf(Target_Service_Level)

Daily_Demand_Stats = (
    df_med_sales
    .groupby('DrugID')['Quantity_Sold']
    .agg(
        avg_daily_demand='mean',
        daily_demand_std='std'
    )
    .reset_index()
)

Daily_Demand_Stats['daily_demand_std']=Daily_Demand_Stats['daily_demand_std'].fillna(0)

Daily_Demand_Stats['Demand_Mean_LeadTime'] = (
    Daily_Demand_Stats['avg_daily_demand'] * Lead_Time_Days
)

Daily_Demand_Stats['Demand_StdDev_LeadTime'] = (
    Daily_Demand_Stats['daily_demand_std'] * np.sqrt(Lead_Time_Days)
)

Daily_Demand_Stats['Safety_Stock'] = (
    Z_Value * Daily_Demand_Stats['Demand_StdDev_LeadTime']
)

Daily_Demand_Stats['Reorder_Point'] = (
    Daily_Demand_Stats['Demand_Mean_LeadTime'] +
    Daily_Demand_Stats['Safety_Stock']
)

Current_Stock = df_med_sales[['DrugID','Drug_Name','Remaining_Stock']].drop_duplicates('DrugID')

Full_Stock_Model = Daily_Demand_Stats.merge(Current_Stock,on='DrugID',how='left')

Full_Stock_Model['Suggested_Reorder_Qty'] = np.maximum(
    0,
    Full_Stock_Model['Demand_Mean_LeadTime'] +
    Full_Stock_Model['Safety_Stock'] -
    Full_Stock_Model['Remaining_Stock']
)

Full_Stock_Model[
    ['Demand_Mean_LeadTime', 'Demand_StdDev_LeadTime',
     'Safety_Stock', 'Reorder_Point', 'Suggested_Reorder_Qty']
] = Full_Stock_Model[
    ['Demand_Mean_LeadTime', 'Demand_StdDev_LeadTime',
     'Safety_Stock', 'Reorder_Point', 'Suggested_Reorder_Qty']
].round().astype(int)

Full_Stock_Model.sort_values(by='Suggested_Reorder_Qty',ascending=False)

Full_Stock_Model['Suggested_Reorder_Qty'] = Full_Stock_Model['Suggested_Reorder_Qty'].clip(lower=0)

Reorder_Table = Full_Stock_Model[['DrugID', 'Remaining_Stock','Reorder_Point','Suggested_Reorder_Qty']].sort_values('Suggested_Reorder_Qty',ascending=False)
Reorder_Table

df_med_sales=df_med_sales.merge(
    Full_Stock_Model[['DrugID','Demand_Mean_LeadTime','Reorder_Point','Safety_Stock']],
    on='DrugID',
    how='left'
)

df_med_sales['Reorder_Flag'] = (
    df_med_sales['Remaining_Stock'] <= df_med_sales['Reorder_Point']
)

df_med_sales['Reorder_Quantity']=(
    df_med_sales['Demand_Mean_LeadTime'] +
    df_med_sales['Safety_Stock'] -
    df_med_sales['Remaining_Stock']
)

reorder_df=df_med_sales[['Date','DrugID','Drug_Name','Quantity_Sold','Remaining_Stock','Stockout_Flag','Reorder_Point','Reorder_Flag','Reorder_Quantity']]

reorder_df=reorder_df.merge(
    df_meds[['DrugID','Manufacturer']],
    on='DrugID',
    how='left'
)

reorder_df

fast20 = (
    df_med_sales.groupby("DrugID")["Quantity_Sold"]
    .sum()
    .sort_values(ascending=False)
    .head(20)
    .reset_index()
)

df_final=reorder_df.merge(fast20['DrugID'],on='DrugID',how='right')

df_final["Reorder_Intensity"] = (
    (df_final["Reorder_Point"] - df_final["Remaining_Stock"]) 
    / df_final["Reorder_Point"]
).clip(lower=0)  # negative becomes 0

df_final.head(3)

df_final["Month"] = df_final["Date"].dt.to_period("M").astype(str)
pivot_df = df_final.pivot_table(
    index="Drug_Name",
    columns="Month",
    values="Reorder_Intensity",
    aggfunc="mean" 
)
fig = px.imshow(
    pivot_df,
    labels=dict(
        x="Month",
        y="Drug Name",
        color="Reorder Intensity (0–1)"
    ),
    title="Reorder Trigger Heatmap — Top 20 Fast-Moving Drugs",
    aspect="auto",
    color_continuous_scale=[
        [0.0,  "#2166ac"],  
        [0.5,  "#ffff33"],   
        [1.0,  "#b2182b"]    
    ]  # Blue = safe , yellow=average , Red = danger
)
fig.update_traces(
    hovertemplate=
        "<b>Drug:</b> %{y}<br>" +
        "<b>Month:</b> %{x}<br>" +
        "<b>Reorder Intensity:</b> %{z:.2f}<br>" +
        "<extra></extra>"
)
fig.update_layout(
    xaxis=dict(side="top"),
    height=850,
    coloraxis_colorbar=dict(
        title="Risk",
        ticks="outside"
    )
)

fig.show()

df_final['Date'] = pd.to_datetime(df_final['Date'])
df_final['Month'] = df_final['Date'].dt.to_period('M').astype(str)

df_plot = df_final.copy()

df_plot['Reorder_Intensity'] = (
    (df_plot['Reorder_Point'] - df_plot['Remaining_Stock']) / df_plot['Reorder_Point']
).clip(lower=0, upper=1)

pivot_columns = {
    "Reorder_Intensity": "mean",
    "Quantity_Sold": "sum",
    "Remaining_Stock": "mean",
    "Reorder_Point": "mean",
    "Reorder_Quantity": "mean"
}

pivots = {}

for col, func in pivot_columns.items():
    pivots[col] = df_plot.pivot_table(
        index="Drug_Name",
        columns="Month",
        values=col,
        aggfunc=func
    )

base = pivots["Reorder_Intensity"]

for col in pivot_columns:
    pivots[col] = pivots[col].reindex(index=base.index, columns=base.columns)

manufacturer_map = (
    df_plot.drop_duplicates("Drug_Name")
           .set_index("Drug_Name")["Manufacturer"]
)

manufacturers_matrix = np.array([
    [manufacturer_map.get(drug, "")] * len(base.columns)
    for drug in base.index
])


reorder_flag = np.where(base.values > 0, "TRUE", "FALSE")

customdata = np.dstack([
    reorder_flag,
    manufacturers_matrix,
    pivots["Remaining_Stock"].values,
    pivots["Reorder_Point"].values,
    pivots["Reorder_Quantity"].values
])

fig = px.imshow(
    base,
    labels=dict(x="Month", y="Drug Name", color="Reorder Intensity (0–1)"),
    title="Reorder Risk Heatmap — Top 20 Fast-Moving Drugs",
    aspect="auto",
    color_continuous_scale=[
        [0.0,  "#2166ac"], 
        [0.5,  "#ffff33"],   
        [1.0,  "#b2182b"]   
    ]
)

fig.data[0].customdata = customdata

fig.update_traces(
    hovertemplate=(
        "<b>Drug:</b> %{y}<br>"
        "<b>Month:</b> %{x}<br>"
        "<b>Reorder Flag:</b> %{customdata[0]}<br>"
        "<b>Manufacturer:</b> %{customdata[1]}<br>"
        "<b>Remaining Stock:</b> %{customdata[2]}<br>"
        "<b>Reorder Point (ROP):</b> %{customdata[3]}<br>"
        "<b>Suggested Reorder Qty (month avg):</b> %{customdata[4]}<extra></extra>"
    )
)

fig.update_layout(
    xaxis=dict(side="top"),
    height=850
)

fig.show()

	Drug_Name	DrugID
0	Augmentin 625 Duo Tablet	1
1	Azithral 500 Tablet	2
2	Ascoril LS Syrup	3
3	Allegra 120mg Tablet	4
4	Avil 25 Tablet	5
...	...	...
249393	Ziyapod 100mg Oral Suspension	249394
249394	Zemhart 30mg Tablet	249395
249395	Zivex 25mg Tablet	249396
249396	ZI Fast 500mg Injection	249397
249397	Zyvocol 1% Dusting Powder	249398

	Date	DrugID	Drug_Name	Quantity_Sold	Unit_Price	Total_Amount
0	2024-01-01	142363	Milrox 150mg Tablet	13	55.0	715.0
1	2024-01-01	127868	Lorsar H 50mg/12.5mg Tablet	11	92.0	1012.0
2	2024-01-01	100693	GMD OF Suspension	13	35.0	455.0
3	2024-01-01	116398	Kapinac SP Tablet	4	69.0	276.0
4	2024-01-01	107081	Ivaril DX Syrup	13	79.0	1027.0

	Drug_Name	Manufacturer	Price	Active_Ingredient	Therapeutic_Class	Action_Class	Primary_Use	Is_Habit_Forming	Mfg_Date	Expiry_Date	Days_Remaining	Stock_Qty	DrugID
0	Augmentin 625 Duo Tablet	Glaxo SmithKline Pharmaceuticals Ltd	223.42	Amoxycillin (500mg)	ANTI INFECTIVES	NaN	Treatment of Bacterial infections	No	2025-07-22	2026-10-11	305	405	1
1	Azithral 500 Tablet	Alembic Pharmaceuticals Ltd	132.36	Azithromycin (500mg)	ANTI INFECTIVES	Macrolides	Treatment of Bacterial infections	No	2025-01-31	2028-01-31	782	280	2
2	Ascoril LS Syrup	Glenmark Pharmaceuticals Ltd	118.00	Ambroxol (30mg/5ml)	RESPIRATORY	NaN	Treatment of Cough with mucus	No	2024-11-10	2026-11-10	335	437	3
3	Allegra 120mg Tablet	Sanofi India Ltd	218.81	Fexofenadine (120mg)	RESPIRATORY	H1 Antihistaminics (second Generation)	Treatment of Sneezing and runny nose due to al...	No	2024-06-19	2025-12-18	8	171	4
4	Allegra 120mg Tablet	Sanofi India Ltd	218.81	Fexofenadine (120mg)	RESPIRATORY	H1 Antihistaminics (second Generation)	Treatment of Sneezing and runny nose due to al...	No	2024-02-16	2027-02-15	432	108	4

	Date	DrugID	Drug_Name	Quantity_Sold	Unit_Price	Total_Amount
0	2024-01-01	142363	Milrox 150mg Tablet	13	55.00	715.0
1	2024-01-01	127868	Lorsar H 50mg/12.5mg Tablet	11	92.00	1012.0
2	2024-01-01	100693	GMD OF Suspension	13	35.00	455.0
3	2024-01-01	116398	Kapinac SP Tablet	4	69.00	276.0
4	2024-01-01	107081	Ivaril DX Syrup	13	79.00	1027.0
...	...	...	...	...	...	...
218752	2025-12-30	125990	Lifobid Infusion	13	120.00	1560.0
218753	2025-12-30	119969	Lozep 2 Tablet	6	27.55	165.3
218754	2025-12-30	218810	Tamrik-D Tablet	1	450.00	450.0
218755	2025-12-30	114203	Kylazine Tablet	11	36.00	396.0
218756	2025-12-30	246333	Zesnil 25mg Tablet	1	70.00	70.0

	DrugID	Drug_Name	Stockout_Count
6	56291	Clavoxin Dry Syrup	4
15	159603	Oral Polio Vaccine	3
20	244778	Zift 250mg Tablet	2
1	10811	Arcip 500mg Tablet	2
11	102166	Homatropine Eye Drop	2
12	103634	Hepasure Infusion	1
19	222705	Theolar 40mg Tablet	1
18	196486	Symbiotik XL 625 Tablet	1
17	182670	Ringer Lactate Infusion	1
16	180678	Qualifest-T 1gm/0.125gm Injection	1
14	149658	NS 0.9% Infusion	1
13	114691	Kezop Soap	1
0	110	Alkasol Oral Solution Sugar Free	1
9	82583	Foracort 400 Rotacap	1
8	74194	Exol Syrup	1
7	59031	Diclolab 75mg Injection	1
5	54358	Cefidaz 50mg Tablet DT	1
4	45689	Chingasu 75mg Injection	1
3	25293	B Bact Ointment	1
2	15881	Ambrozen-S Syrup	1

1.Final_Pharmacy_Inventory_Extended.csv --> df_meds¶

2.Synthetic Sales Table (Jan 2024 – Dec 2025) --> df_med_sales¶

Database¶

Import Liabaries¶

Collect and Clean Data¶

Loading into MySQL dataset¶

1.Which medicines frequently go out of stock?¶

Frequently stock-out drugs:¶

2.Sales Pattern for Top10 Fast moving drugs¶

Sales Pattern for Top 10 Fast-Moving Drugs¶

3.Which drugs are causing financial loss due to expiry?¶

Top contributors:¶

4.1.Total Loss by Expiry¶

Total Loss by Expired drugs : 1.67 Billion

4.2.Total Loss by stocking out¶

Observation¶

How to Reduce Stock-out Loss¶

takeaway¶

Total Loss by Stockout drugs: 27.34 Thousand

4.3 Top 10 drugs causing highest expiry loss¶

5.Expiry Loss per therapeutic class¶

6.ABC Analysis¶

assigning ABC category¶

Key Insight:¶

7.Reorder Quantity Recommendations¶

Created Reorder_Table with:¶

Safety_Stock¶

Reorder_Point (ROP)¶

Remaining_Stock¶

Suggested_Reorder_Qty (Reorder_Quantity)¶

7.1.How much quantity should be reordered for each drug to prevent stock-outs?¶

8.Reorder Flagging, when Remaining_Stock gets close to i.e. <= Reorder_Point¶

8.1.Reorder Trigger Heatmap — Top 20 Fast-Moving Drugs¶

Reorder Flag = TRUE¶

Reorder Trigger Heatmap (Top 20 Fast-Moving Drugs):¶

	Drug_Name	Loss_by_expiry
0	Keytruda Injection	-66929500.00
1	Crizalk 200mg Capsule	-44378632.50
2	Avastin 400mg Injection	-38816250.00
3	Abiron 250mg Tablet	-32760000.00
4	Herclon Injection	-32350406.25
5	Neuzumab 440mg Injection	-26291250.00
6	Alkeprost 250mg Tablet	-24403500.00
7	Biceltis 440mg Injection	-21600000.00
8	Bevacirel 400mg Injection	-19078462.94
9	Nucala Injection	-15571140.00

	Drug_Name	Loss_by_stockout
0	Oral Polio Vaccine	-9350.00
1	Clavoxin Dry Syrup	-5815.00
2	Qualifest-T 1gm/0.125gm Injection	-2960.00
3	Symbiotik XL 625 Tablet	-1567.16
4	Hepasure Infusion	-1000.00
5	Arcip 500mg Tablet	-990.00
6	Zift 250mg Tablet	-750.00
7	Homatropine Eye Drop	-746.60
8	Ringer Lactate Infusion	-696.08
9	B Bact Ointment	-636.00

	ABC_Category	Total_Revenue	Drug_List	Category_count
0	A	4.846973e+07	ACTEMRA 400 MG INJECTION, Abatitor 250mg Table...	907
1	B	7.355068e+07	ACTEMRA 200 MG INJECTION, ACTEMRA 80 MG INJECT...	12686
2	C	1.220735e+08	A 1 5mg Tablet, A C Ford 200mg Tablet, A Clin ...	2032846

	DrugID	avg_daily_demand	daily_demand_std	Demand_Mean_LeadTime	Demand_StdDev_LeadTime	Safety_Stock	Reorder_Point	Drug_Name	Remaining_Stock	Suggested_Reorder_Qty
85021	242323	14.0	0.000000	98	0	0	98	Zithium XL 100mg Suspension	26	72
33663	96001	14.0	0.000000	98	0	0	98	Glimicer M2 Forte Tablet SR	26	72
53112	151371	14.0	0.000000	98	0	0	98	Ndase Tablet	26	72
5618	15881	14.0	0.000000	98	0	0	98	Ambrozen-S Syrup	26	72
42471	120945	14.0	0.000000	98	0	0	98	Levroxa Syrup	26	72
...	...	...	...	...	...	...	...	...	...	...
30068	85612	3.0	0.000000	21	0	0	21	Fenase Tablet	469	0
30067	85611	6.0	0.000000	42	0	0	42	Fensaide P 50mg/500mg Tablet	466	0
30066	85607	9.0	0.000000	63	0	0	63	Flura 200mg Tablet	403	0
30065	85602	4.0	0.000000	28	0	0	28	Finide 180mg Tablet	326	0
87477	249397	7.0	6.082763	49	16	26	75	ZI Fast 500mg Injection	312	0

	Date	DrugID	Drug_Name	Quantity_Sold	Remaining_Stock	Stockout_Flag	Reorder_Point	Reorder_Flag	Reorder_Quantity	Manufacturer
0	2025-12-19	1	Augmentin 625 Duo Tablet	6	399	False	42	False	-357	Glaxo SmithKline Pharmaceuticals Ltd
1	2024-09-10	4	Allegra 120mg Tablet	5	117	False	67	False	-50	Sanofi India Ltd
2	2024-09-10	4	Allegra 120mg Tablet	5	117	False	67	False	-50	Sanofi India Ltd
3	2024-09-10	4	Allegra 120mg Tablet	5	117	False	67	False	-50	Sanofi India Ltd
4	2024-09-10	4	Allegra 120mg Tablet	5	117	False	67	False	-50	Sanofi India Ltd
...	...	...	...	...	...	...	...	...	...	...
132153	2025-03-23	249389	Zunix 50mg Dry Syrup	13	58	False	91	True	33	Vesnik Molecules
132154	2024-10-06	249390	Ziyapod 200mg Tablet DT	6	289	False	42	False	-247	Ziyana Lifesciences Pvt Ltd
132155	2024-09-08	249397	ZI Fast 500mg Injection	4	312	False	75	False	-237	Burgeon Health Series Private Limited
132156	2024-10-16	249397	ZI Fast 500mg Injection	14	298	False	75	False	-223	Burgeon Health Series Private Limited
132157	2025-03-18	249397	ZI Fast 500mg Injection	3	295	False	75	False	-220	Burgeon Health Series Private Limited

	Date	DrugID	Drug_Name	Quantity_Sold	Remaining_Stock	Stockout_Flag	Reorder_Point	Reorder_Flag	Reorder_Quantity	Manufacturer
0	2024-03-19	182670	Ringer Lactate Infusion	9	101	False	73	False	-28	Baxter India Pvt Ltd
1	2024-03-19	182670	Ringer Lactate Infusion	9	101	False	73	False	-28	Parenteral Drugs India Ltd
2	2024-03-19	182670	Ringer Lactate Infusion	9	101	False	73	False	-28	Parenteral Drugs India Ltd